package DTO;

import global.global;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import database.DatabaseHelper;

public class SignDTO {
	
	private String ID;
	private String Name;
	private String Number;
	private String TypeID;
	private String ImagePath;
	private String Content;
	private String TypeName;
	
	//Property ID
	public String getID(){return ID;}
	public void setID(String id){ID = id;}
	
	//Property Name
	public String getName(){return Name;}
	public void setName(String name){Name = name;}
	
	//Property Name
	public String getNumber(){return Number;}
	public void setNumber(String number){Number = number;}
	
	//Property TypeID
	public String getTypeID(){return TypeID;}
	public void setTypeID(String typeID){TypeID = typeID;}
	
	//Property TypeName
	public String getTypeName() { return TypeName; }
	public void setTypeName(String typeName) { TypeName = typeName; }
	
	//Property ImagePath
	public String getImagePath(){return ImagePath;}
	public void setImagePath(String imagePath){ImagePath = imagePath;}
	
	//Property Content
	public String getContent(){return Content;}
	public void setContent(String content){Content = content;}
	
	/*private String createID(int number)
	{
		String result = "SG";
		if(number<10)
		{
			result+="000"+number;
		}
		else if(number<100)
		{
			result+="00"+number;
		}else if(number<1000)
		{
			result+="0"+number;
		}else
		{
			result+=number;
		}
		return result;
	}*/
	
	//Get Sign by ID
	public static SignDTO getSignByID(Context context, String ID)
	{
		SignDTO result = new SignDTO();
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	String query = "SELECT Signs.ID, Signs.Name, Signs.Number, Signs.TypeID, Signs.Content, Signs.Image, SignTypes.Name AS NameType FROM Signs LEFT JOIN SignTypes ON Signs.TypeID = SignTypes.ID WHERE Signs.ID = '" + ID + "'";
	 	Cursor cursor = myDbHelper.getMyDataBase().rawQuery(query, null);
		if(cursor.moveToFirst()){
			//Set value to properties
			result.setID(cursor.getString(cursor.getColumnIndex("ID")));
			result.setName(cursor.getString(cursor.getColumnIndex("Name")));
			result.setTypeName(cursor.getString(cursor.getColumnIndex("NameType")));
			result.setNumber(cursor.getString(cursor.getColumnIndex("Number")));
			result.setTypeID(cursor.getString(cursor.getColumnIndex("TypeID")));
			result.setImagePath(cursor.getString(cursor.getColumnIndex("Image")));
			result.setContent(cursor.getString(cursor.getColumnIndex("Content")));
		}
	 	myDbHelper.close();
	 	return result;
	}
	
	//Get all sign
	public static ArrayList<SignDTO> getSignBySignType(Context context, String SignTypeID)
	{
		ArrayList<SignDTO> result = new ArrayList<SignDTO>();
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	String query = null;
	 	if(SignTypeID.equals("Tất cả")){
	 		query = "SELECT * FROM Signs";
	 	}else{
	 		query = "SELECT * FROM Signs WHERE TypeID = '" + SignTypeID + "'";
	 	}
	 	
	 	Cursor cursor = myDbHelper.getMyDataBase().rawQuery(query, null);
		if(cursor.moveToFirst()){
			//Set value to properties
			do{
				SignDTO signDTO = new SignDTO();
				signDTO.setID(cursor.getString(cursor.getColumnIndex("ID")));
				signDTO.setName(cursor.getString(cursor.getColumnIndex("Name")));
				signDTO.setNumber(cursor.getString(cursor.getColumnIndex("Number")));
				signDTO.setTypeID(cursor.getString(cursor.getColumnIndex("TypeID")));
				SignTypeDTO signTypeDTO = new SignTypeDTO();
				signTypeDTO = SignTypeDTO.getSignTypeByID(context, signDTO.getTypeID());
				signDTO.setTypeName(signTypeDTO.getName());
				signDTO.setImagePath(cursor.getString(cursor.getColumnIndex("Image")));
				signDTO.setContent(cursor.getString(cursor.getColumnIndex("Content")));
				result.add(signDTO);
			}while(cursor.moveToNext());
		}
	 	myDbHelper.close();
	 	return result;
	}

	//Get sign by key word
	public static ArrayList<SignDTO> getSignByKeyWord(Context context, String keywords, String SignTypeID)
	{
		
		String query = "";
		if(keywords == null || keywords.isEmpty())
		{
			if(SignTypeID.equals("Tất cả")){
				query = "SELECT Signs.ID,Number, TypeID, Signs.Name, Image, Signs.Content, SignTypes.Name As TypeName FROM Signs LEFT JOIN SignTypes ON Signs.TypeID = SignTypes.ID";
			}else{
				query = "SELECT Signs.ID,Number, TypeID, Signs.Name, Image, Signs.Content, SignTypes.Name As TypeName FROM Signs LEFT JOIN SignTypes ON Signs.TypeID = SignTypes.ID WHERE TypeID = '" + SignTypeID + "'";
			}
			
		}
		else
		{
			keywords = global.removeAccent(keywords);
			if(SignTypeID.equals("Tất cả")){
				query = "SELECT Signs.ID,Number, TypeID, Signs.Name, Image, Signs.Content, SignTypes.Name As TypeName FROM Signs LEFT JOIN SignTypes ON Signs.TypeID = SignTypes.ID "+
						"WHERE Signs.UnsignContent like '%"+ keywords.trim()+"%' "+
						"or Signs.UnsignName like '%"+ keywords.trim()+"%' "+
						"or SignTypes.UnsignName like '%"+ keywords.trim()+"%' "+
						"or Number like '%"+ keywords.trim()+"%'";
		
			}else{
				query = "SELECT Signs.ID,Number, TypeID, Signs.Name, Image, Signs.Content, SignTypes.Name As TypeName FROM Signs LEFT JOIN SignTypes ON Signs.TypeID = SignTypes.ID "+
						"WHERE ( UnsignContent like '%"+ keywords.trim()+"%' "+
						"or Signs.UnsignName like '%"+ keywords.trim()+"%' "+
						"or SignTypes.UnsignName like '%"+ keywords.trim()+"%' " +
						"or Number like '%"+ keywords.trim()+"%')"+
						"and TypeID = '" + SignTypeID + "'";
			}
		}
		ArrayList<SignDTO> arrResults = new ArrayList<SignDTO>();
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	///////////////////////////////////////////
	 	Cursor cursor = myDbHelper.getMyDataBase().rawQuery(query, null);
		if(cursor.moveToFirst()){
			do{
				SignDTO resultItem = new SignDTO();

				//Set properties
				resultItem.setID(cursor.getString(cursor.getColumnIndex("ID")));
				resultItem.setTypeID(cursor.getString(cursor.getColumnIndex("TypeID")));
				resultItem.setName(cursor.getString(cursor.getColumnIndex("Name")));
				resultItem.setContent(cursor.getString(cursor.getColumnIndex("Content")));
				resultItem.setNumber(cursor.getString(cursor.getColumnIndex("Number")));
				//SignTypeDTO signTypeDTO = new SignTypeDTO();
				//signTypeDTO = SignTypeDTO.getSignTypeDTO(context, resultItem.getTypeID());
				try{
				resultItem.setTypeName(cursor.getString(cursor.getColumnIndex("TypeName")));
				}catch(Exception e){}
				
	 			try{
	 				resultItem.setImagePath(cursor.getString(cursor.getColumnIndex("Image")));
	 			}catch(Exception e){
	 				resultItem.setImagePath("");
	 			}
	 			
				arrResults.add(resultItem);
			}while(cursor.moveToNext());
		}
	 	myDbHelper.close();
	 	return arrResults;
	}
}
